﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using JKF.Substructure;
using System.Data.SqlClient;
using System.Data.OleDb;
using JKF.BusinessLayer.BLL;
namespace JKF.Web.Customer
{
    public partial class Import : PageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
            //获取上传文件的路径
            string filepath = file.FileName;

            string fileType = filepath.Substring(filepath.LastIndexOf(".") + 1);

            if (string.IsNullOrEmpty(fileType))
            {
                Response.Write("<script language=javascript>alert('请选择要导入的数据文件！');</script>");
                return;
            }
            if (fileType != "xls")
            {
                Response.Write("<script language=javascript>alert('上传的文件格式不正确,只能上传“xls”格式的文件！');</script>");
                return;
            }
            else
            {
                // 获取上传文件名称
                string filename = filepath.Substring(filepath.LastIndexOf("\\") + 1);
                //保存到服务器的路径
                string serverpath = Server.MapPath("~/UploadFiles/") + filename;
                //确定上传文件
                file.SaveAs(serverpath);
                string strpath = Server.MapPath("~/UploadFiles/") + filename;

                BindData(strpath);
            }

        }

        public void BindData(string strpath)
        {
            DataTable dt = LoadDataFromExcel(strpath).Tables[0];

            if ((dt.Columns[0].ColumnName != "顾客卡号") 
                || (dt.Columns[1].ColumnName != "顾客级别") 
                || (dt.Columns[2].ColumnName != "姓名") 
                || (dt.Columns[3].ColumnName != "手机号码") 
                || (dt.Columns[4].ColumnName != "身份证")
                || (dt.Columns[5].ColumnName != "性别")
                || (dt.Columns[6].ColumnName != "生日")
                || (dt.Columns[7].ColumnName != "地址")
                || (dt.Columns[8].ColumnName != "邮编")
                || (dt.Columns[9].ColumnName != "电子邮件")
                || (dt.Columns[10].ColumnName != "固定电话")
                || (dt.Columns[11].ColumnName != "顾客分组")
                || (dt.Columns[12].ColumnName != "备注")               
                )
            {
                Response.Write("<script language=javascript>alert('导入格式不正确,请下载excel导入模板，在模板上编辑数据!');</script>");
                return;
            }
            else
            {
                if (dt.Rows.Count > 0)
                {
                    string sqlText = "";
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if (i == 0)
                        {
                            sqlText = sqlText + "select '" + dt.Rows[i]["顾客卡号"].ToString().Trim().Replace(" ","") + "' as CARD_NO,";
                            sqlText = sqlText +"'"+dt.Rows[i]["顾客级别"].ToString().Trim().Replace(" ", "") + "' as LEVEL_CODE,";
                            sqlText = sqlText + "'" + dt.Rows[i]["姓名"].ToString().Trim().Replace(" ", "") + "' as NAME,";
                            sqlText = sqlText + "'" + dt.Rows[i]["手机号码"].ToString().Trim().Replace(" ", "") + "' as MOBILE,";
                            sqlText = sqlText + "'" + dt.Rows[i]["身份证"].ToString().Trim().Replace(" ", "") + "' as ID_CARD,";
                            sqlText = sqlText + "'" + dt.Rows[i]["性别"].ToString().Trim().Replace(" ", "") + "' as SEX,";
                            sqlText = sqlText + "'" + dt.Rows[i]["生日"].ToString().Trim().Replace(" ", "") + "' as BIRTHDAY,";
                            sqlText = sqlText + "'" + dt.Rows[i]["地址"].ToString().Trim().Replace(" ", "") + "' as ADDRESS,";
                            sqlText = sqlText + "'" + dt.Rows[i]["邮编"].ToString().Trim().Replace(" ", "") + "' as POSTAL_CODE,";
                            sqlText = sqlText + "'" + dt.Rows[i]["电子邮件"].ToString().Trim().Replace(" ", "") + "' as EMAIL,";
                            sqlText = sqlText + "'" + dt.Rows[i]["固定电话"].ToString().Trim().Replace(" ", "") + "' as PHONE,";
                            sqlText = sqlText + "'" + dt.Rows[i]["顾客分组"].ToString().Trim().Replace(" ", "") + "' as GROUP_KEY,";
                            sqlText = sqlText + "'" + dt.Rows[i]["备注"].ToString().Trim().Replace(" ", "") + "' as MEMO ";
                        }
                        else
                        {
                            sqlText = sqlText + " union select '" + dt.Rows[i]["顾客卡号"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["顾客级别"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["姓名"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["手机号码"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["身份证"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["性别"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["生日"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["地址"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["邮编"].ToString().Trim().Replace(" ", "") + "' ,";
                            sqlText = sqlText + "'" + dt.Rows[i]["电子邮件"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["固定电话"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["顾客分组"].ToString().Trim().Replace(" ", "") + "',";
                            sqlText = sqlText + "'" + dt.Rows[i]["备注"].ToString().Trim().Replace(" ", "") + "'";
                        }
                    }

                    bllSWING_CARD swingCard = new bllSWING_CARD();
                    DataTable dt2= swingCard.ImportCustomer(sqlText).Tables[0];
                    if (dt2.Rows.Count > 0)
                    {
                        Response.Write("<script language=javascript>alert('导入成功!');</script>");

                        IDictionary<string, string> columns = new Dictionary<string, string>();
                        columns.Add("CARD_NO", "顾客卡号");
                        columns.Add("LEVEL_CODE", "顾客级别");
                        columns.Add("name", "姓名");
                        columns.Add("MOBILE", "手机号码");        
                        columns.Add("ID_CARD", "身份证");
                        columns.Add("SEX", "性别");
                        columns.Add("BIRTHDAY", "生日");
                        columns.Add("ADDRESS", "地址");                        
                        columns.Add("POSTAL_CODE", "邮编");
                        columns.Add("EMAIL", "电子邮件");
                        columns.Add("PHONE", "固定电话");
                        columns.Add("GROUP_KEY", "顾客分组");
                        columns.Add("MEMO", "备注");
                        columns.Add("reason", "导入失败原因");
                        Excel.Export("导入失败人员", dt2, columns, Response, Excel.PageType.aspx);
                    }
                    else
                    {
                        Response.Write("<script language=javascript>alert('全部导入导入成功!');</script>");
                    }
                }
                else
                {
                    Response.Write("<script language=javascript>alert('没有可导入的数据!');</script>");
                    return;
                }
            }
        }

        //加载Excel 
        public static DataSet LoadDataFromExcel(string filePath)
        {
            try
            {
                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                DataTable schemaTable = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string tbname = schemaTable.Rows[0]["TABLE_NAME"].ToString();
                String sql = "SELECT * FROM  [" + tbname + "]";//可是更改Sheet名称，比如sheet2，等等 

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet OleDsExcle = new DataSet();
                OleDaExcel.Fill(OleDsExcle, tbname.Substring(0, tbname.Length - 1));
                OleConn.Close();
                return OleDsExcle;
            }
            catch (Exception err)
            {
                throw new Exception(err.Message);
            }
        }
    }
}